import json

import pandas as pd
from datetime import datetime, timedelta

repo_name_list = ["cmssw","django","kubernetes","laravel","moby","opencv","pandas","rails","react","rust","salt","scikit-learn","symfony","tensorflow","terraform","yii2"]
#repo_name_list = ["scikit-learn"]
for repo_name in repo_name_list:
    # 读取Excel文件
    df = pd.read_excel('./sim_data/'+repo_name+'/'+repo_name+'_pr_mP.xlsx')

    # 将时间戳字符串转换为datetime对象
    df['created_time'] = pd.to_datetime(df['created_time'])
    df['closed_time'] = pd.to_datetime(df['closed_time'], errors='coerce')
    df['merged_time'] = pd.to_datetime(df['merged_time'], errors='coerce')


    def extract_comment_times(row):
        comment_time = row['comment_time']

        if isinstance(comment_time, str) and comment_time.startswith('['):
            try:
                # 将单引号替换为双引号
                comment_time = comment_time.replace("'", '"')
                # 将字符串转换为Python对象列表
                comment_dicts = json.loads(comment_time)

                # 提取并转换所有created_at时间戳
                all_comment_dates = [pd.to_datetime(d['created_at']) for d in comment_dicts if 'created_at' in d]

                # 返回一个包含所有时间戳的列表
                return all_comment_dates
            except (json.JSONDecodeError, KeyError):
                return pd.NaT  # 或者返回None，取决于你的具体需求
        elif isinstance(comment_time, str):  # 直接是日期时间字符串的情况
            return pd.to_datetime(comment_time)
        else:
            return pd.NaT  # 对于其他不可识别的数据类型，返回NaT或None


    df['all_comment_times'] = df.apply(extract_comment_times, axis=1)

    # 将非空的评论时间列表转为统一的可迭代形式并explode
    df['exploded_comment_times'] = df['all_comment_times'].apply(lambda x: x if isinstance(x, list) else [x])
    df_expanded_comments = df[df['exploded_comment_times'].map(len).gt(0)].copy()

    # # 确保索引唯一
    # df_expanded_comments = df_expanded_comments.reset_index(drop=True)
    #
    # # 然后进行explode操作
    # df_expanded_comments['exploded_comment_times'] = df_expanded_comments['exploded_comment_times'].explode()

    # 定义PR类型耗时字典
    type_duration_dict = {0: 10, 1: 5, 2: 10, 3: 5, 4: 15, 5: 15}


    # 定义按行数计算耗时函数
    def calculate_duration_by_lines(lines):
        if lines <= 100:
            return 10
        elif lines <= 300:
            return 15
        elif lines <= 500:
            return 20
        elif lines <= 1000:
            return 30
        else:
            return 45


    # 遍历每天的数据统计各项指标
    daily_stats = {}
    start_date = pd.Timestamp(datetime(2022, 1, 1))
    end_date = pd.Timestamp(datetime(2022, 12, 31))

    for date in pd.date_range(start=start_date, end=end_date):
        date_str = date.strftime('%Y-%m-%d')
        date = pd.Timestamp(date)

        # # 每天评审的PR个数（考虑了comment_time中多种格式的情况）
        reviewed_prs = df_expanded_comments[df_expanded_comments['exploded_comment_times'].apply(
            lambda x: any(pd.to_datetime(d).date() == date.date() for d in x)
        )]['pr_number'].nunique()
        reviewed_prs += df[(df['closed_time'].dt.date == pd.Timestamp(date).date()) & (df['comments'] == 0)][
            'pr_number'].nunique()

        # 每天处于open状态的PR个数
        open_prs = df[
            (df['created_time'].dt.date <= date.date()) &
            ((df['closed_time'].isnull()) |
             (df['closed_time'].dt.date >= pd.Timestamp(date).date()))
            ]['pr_number'].nunique()

        # 每天合入的PR个数
        merged_prs = df[df['merged_time'].dt.date == date.date()]['pr_number'].nunique()

        # 每天抛弃的PR个数
        abandoned_prs = df[(df['closed_time'].dt.date == date.date()) & df['merged_time'].isnull()][
            'pr_number'].nunique()

        # 计算评审耗时（此处仅展示第一种方式，根据类型计算）
        # 计算没有comments但在当天关闭的PR评审耗时
        closed_without_comment = df[(df['closed_time'].dt.date == date.date()) & df['comments'].eq(0)]
        reviewed_durations_extra = closed_without_comment['PR_type'].map(type_duration_dict).sum()

        day_df = df[(df['created_time'].dt.date <= date.date()) & ((df['closed_time'].isnull()) |
                                                                   (df['closed_time'].dt.date >= pd.Timestamp(
                                                                       date).date()))]
        # 对于根据类型计算评审耗时（此处仅为示例）
        reviewed_durations_type_all_comments = 0
        for index, row in day_df.iterrows():
            # 处理 all_comment_times 字段，确保其为一个包含日期的列表
            comment_dates = []
            if isinstance(row['all_comment_times'], list):
                for t in row['all_comment_times']:
                    try:
                        comment_date = pd.to_datetime(t).date()
                        if comment_date == date.date():
                            comment_dates.append(comment_date)
                    except (ValueError, TypeError):
                        pass  # 忽略无法转换为日期的时间戳
            elif isinstance(row['all_comment_times'], pd.Timestamp) and row['all_comment_times'].date() == date.date():
                comment_dates.append(row['all_comment_times'].date())

            # 如果有当天的评论，则根据PR类型计算并累加耗时
            if comment_dates:
                reviewed_durations_type_all_comments += type_duration_dict[row['PR_type']] * len(comment_dates)

        reviewed_durations_type_all_comments += reviewed_durations_extra

        # 计算评审耗时（此处仅展示第二种方式，根据行数计算，有一个'change_line'字段记录行数变化）
        # 计算没有comments但在当天关闭的PR评审耗时（按行数计算）
        closed_without_comment = df[(df['closed_time'].dt.date == date.date()) & df['comments'].eq(0)]
        reviewed_durations_extra_lines = closed_without_comment['change_line'].apply(calculate_duration_by_lines).sum()

        reviewed_durations_lines = 0
        for index, row in day_df.iterrows():
            # 同样处理 all_comment_times 字段，确保其为一个包含日期的列表
            comment_dates = []
            if isinstance(row['all_comment_times'], list):
                for t in row['all_comment_times']:
                    try:
                        comment_date = pd.to_datetime(t).date()
                        if comment_date == date.date():
                            comment_dates.append(comment_date)
                    except (ValueError, TypeError):
                        pass  # 忽略无法转换为日期的时间戳
            elif isinstance(row['all_comment_times'], pd.Timestamp) and row['all_comment_times'].date() == date.date():
                comment_dates.append(row['all_comment_times'].date())

            # 如果有当天的评论，则根据行数变化量计算并累加耗时
            if comment_dates:
                reviewed_durations_lines += calculate_duration_by_lines(row['change_line']) * len(
                    comment_dates)

        reviewed_durations_lines += reviewed_durations_extra_lines

        # 计算每天应评审的总耗时（基于类型）
        daily_expected_review_durations_sum_type = 0

        for index, row in day_df.iterrows():
            # 处理 all_comment_times 字段，确保其为一个包含日期的列表
            comment_dates = []
            if isinstance(row['all_comment_times'], list):
                for t in row['all_comment_times']:
                    try:
                        comment_date = pd.to_datetime(t).date()
                        if comment_date >= date.date():
                            comment_dates.append(comment_date)
                    except (ValueError, TypeError):
                        pass  # 忽略无法转换为日期的时间戳
            elif isinstance(row['all_comment_times'], pd.Timestamp) and row['all_comment_times'].date() >= date.date():
                comment_dates.append(row['all_comment_times'].date())

            # 如果有当天或之后的评论，则根据PR类型计算并累加预期评审耗时
            if comment_dates:
                expected_reviews_for_this_pr = len(comment_dates)
                daily_expected_review_durations_sum_type += type_duration_dict[
                                                                row['PR_type']] * expected_reviews_for_this_pr

        # 对于没有comments但在当天关闭的PR，每个PR加上一次评审耗时
        closed_without_comment = df[(df['closed_time'].dt.date == date.date()) & df['comments'].eq(0)]
        daily_expected_review_durations_extra_type = closed_without_comment['PR_type'].map(type_duration_dict).sum()
        # 合并计算每天应评审的总耗时（类型方式）
        daily_expected_review_durations_sum_type += daily_expected_review_durations_extra_type

        # 计算每天应评审的总耗时（基于行数）
        daily_expected_review_durations_sum_lines = 0

        for index, row in day_df.iterrows():
            # 处理 all_comment_times 字段，确保其为一个包含日期的列表
            comment_dates = []
            if isinstance(row['all_comment_times'], list):
                for t in row['all_comment_times']:
                    try:
                        comment_date = pd.to_datetime(t).date()
                        if comment_date >= date.date():
                            comment_dates.append(comment_date)
                    except (ValueError, TypeError):
                        pass  # 忽略无法转换为日期的时间戳
            elif isinstance(row['all_comment_times'], pd.Timestamp) and row['all_comment_times'].date() >= date.date():
                comment_dates.append(row['all_comment_times'].date())

            # 如果有当天或之后的评论，则根据PR的change_line计算并累加预期评审耗时
            if comment_dates:
                expected_reviews_for_this_pr = len(comment_dates)
                daily_expected_review_durations_sum_lines += calculate_duration_by_lines(
                    row['change_line']) * expected_reviews_for_this_pr

        # 对于没有comments但在当天关闭的PR，每个PR加上一次评审耗时（按行数计算）
        closed_without_comment = df[(df['closed_time'].dt.date == date.date()) & df['comments'].eq(0)]
        reviewed_durations_extra_lines = closed_without_comment['change_line'].apply(calculate_duration_by_lines).sum()

        # 合并计算每天应评审的总耗时（行数方式）
        daily_expected_review_durations_sum_lines += reviewed_durations_extra_lines

        # 更新每日统计数据
        daily_stats[date_str] = {
            'reviewed_prs': reviewed_prs,
            'open_prs': open_prs,
            'merged_prs': merged_prs,
            'abandoned_prs': abandoned_prs,
            'reviewed_durations_type': reviewed_durations_type_all_comments,
            'reviewed_durations_lines': reviewed_durations_lines,
            'expected_review_durations_type': daily_expected_review_durations_sum_type,
            'expected_review_durations_lines': daily_expected_review_durations_sum_lines,
        }

    # 计算全年平均值
    annual_type_averages = {}
    annual_lines_averages = {}

    # Ec1计算
    annual_type_averages['Ec1'] = sum(
        stats['reviewed_prs'] / max(stats['open_prs'],1) for stats in daily_stats.values()) / len(
        daily_stats)
    annual_lines_averages['Ec1'] = annual_type_averages['Ec1']

    # 计算EC2（类型方式）
    annual_type_averages['Ec2_type'] = sum(stats['reviewed_durations_type'] / max(stats['expected_review_durations_type'], 1)
                                           for stats in daily_stats.values() if
                                           stats['expected_review_durations_type'] > 0) / len(daily_stats)

    # 计算EC2（行数方式）
    annual_lines_averages['Ec2_lines'] = sum(
        stats['reviewed_durations_lines'] / stats['expected_review_durations_lines']
        for stats in daily_stats.values() if
        stats['expected_review_durations_lines'] > 0) / len(daily_stats)

    # Ed1计算
    annual_type_averages['Ed1'] = sum(
        stats['merged_prs'] / max(stats['reviewed_prs'], 1) for stats in daily_stats.values()) / len(
        daily_stats)
    annual_lines_averages['Ed1'] = annual_type_averages['Ed1']

    # Ed2计算
    annual_type_averages['Ed2'] = sum(
        stats['abandoned_prs'] / max(stats['reviewed_prs'], 1) for stats in daily_stats.values()) / len(
        daily_stats)
    annual_lines_averages['Ed2'] = annual_type_averages['Ed2']

    # sum计算
    annual_type_averages['sum'] = annual_type_averages['Ec1'] + annual_type_averages['Ec2_type'] + annual_type_averages[
        'Ed1'] + annual_type_averages['Ed2']
    annual_lines_averages['sum'] = annual_lines_averages['Ec1'] + annual_lines_averages['Ec2_lines'] + \
                                   annual_lines_averages['Ed1'] + annual_lines_averages['Ed2']

    print(repo_name+"类型：", annual_type_averages)
    print(repo_name+"行数：", annual_lines_averages)
    print(repo_name+"2022年全年平均open======"+str(sum(stats['open_prs'] for stats in daily_stats.values()) / len(daily_stats)))
    print("=====================================")
    # 将 annual_type_averages 和 annual_lines_averages 合并至一个csv文件，标题为：类别， Ec1, Ec2, Ed1, Ed2, sum
    # 创建一个新的DataFrame，包含所有年度平均指标
    annual_averages_df = pd.DataFrame({
        'type': ['基于类型', '基于行数'],
        'Ec1': [annual_type_averages['Ec1'], annual_lines_averages['Ec1']],
        'Ec2': [annual_type_averages['Ec2_type'], annual_lines_averages['Ec2_lines']],
        'Ed1': [annual_type_averages['Ed1'], annual_lines_averages['Ed1']],
        'Ed2': [annual_type_averages['Ed2'], annual_lines_averages['Ed2']],
        'sum': [annual_type_averages['sum'], annual_lines_averages['sum']]
    })

    # 将DataFrame写入CSV文件
    annual_averages_df.to_csv('./sim_data/'+repo_name+'/result/'+repo_name+'_annual_averages.csv', index=False)

